package com.moo2u2.android.oaktononthego.timesheet.dao;

import java.text.SimpleDateFormat;
import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.CursorIndexOutOfBoundsException;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class TimesheetDbAdapter {

	public static final String DB_DATE_FORMAT = "yyyy-MM-dd";// HH:mm:ss";

	private Context context;

	private SQLiteDatabase db;

	private TimesheetDatabaseHelper dbHelper;

	public TimesheetDbAdapter(Context context) {
		this.context = context;
	}

	public TimesheetDbAdapter open() throws SQLException {
		dbHelper = new TimesheetDatabaseHelper(context);
		db = dbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		dbHelper.close();
	}

	public long createTimesheetEntry(long timesheet, int day, String type,
			String subtype, int hours, String notes) {
		ContentValues values = createEntryValues(timesheet, day, type, subtype,
				hours, notes);

		return db.insert(TimesheetEntryTable.ENTRY_TABLE, null, values);
	}

	public long createTimesheet(Date endDate, boolean submitted) {
		ContentValues values = createTimesheetValues(endDate, submitted);

		return db.insert(TimesheetTable.TIMESHEET_TABLE, null, values);
	}

	public boolean updateTimesheetEntry(long rowId, long timesheet, int day,
			String type, String subtype, int hours, String notes) {
		ContentValues values = createEntryValues(timesheet, day, type, subtype,
				hours, notes);

		return db.update(TimesheetEntryTable.ENTRY_TABLE, values,
				TimesheetEntryTable.KEY_ROWID + "=" + rowId, null) > 0;
	}

	public boolean updateTimesheet(Date endDate, boolean submitted) {
		ContentValues values = new ContentValues();
		values.put(TimesheetTable.KEY_SUBMITTED, submitted);

		return db.update(TimesheetTable.TIMESHEET_TABLE, values,
				TimesheetTable.KEY_ENDDATE + " = '"
						+ new SimpleDateFormat(DB_DATE_FORMAT).format(endDate)
						+ "'", null) > 0;
	}

	public boolean deleteTimesheet(long rowId) {
		return db.delete(TimesheetTable.TIMESHEET_TABLE,
				TimesheetTable.KEY_ROWID + " = " + rowId, null) > 0;
	}

	public boolean deleteTimesheetEntry(long rowId) {
		return db.delete(TimesheetEntryTable.ENTRY_TABLE,
				TimesheetEntryTable.KEY_ROWID + " = " + rowId, null) > 0;
	}

	public Cursor fetchDayEntries(Date endDate, int day) {
		Cursor mCursor = db.query(true, TimesheetEntryTable.ENTRY_TABLE
				+ " LEFT JOIN " + TimesheetTable.TIMESHEET_TABLE + " ON "
				+ TimesheetEntryTable.ENTRY_TABLE + "."
				+ TimesheetEntryTable.KEY_TIMESHEET + " = "
				+ TimesheetTable.TIMESHEET_TABLE + "."
				+ TimesheetTable.KEY_ROWID, new String[] {
				TimesheetEntryTable.KEY_ROWID, TimesheetEntryTable.KEY_TYPE,
				TimesheetEntryTable.KEY_SUBTYPE, TimesheetEntryTable.KEY_HOURS,
				TimesheetEntryTable.KEY_NOTES }, TimesheetTable.KEY_ENDDATE
				+ " = '" + new SimpleDateFormat(DB_DATE_FORMAT).format(endDate)
				+ "' AND " + TimesheetEntryTable.KEY_DAY + " = " + day, null,
				null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	public Cursor fetchAllTimesheets() {
		return db.query(TimesheetTable.TIMESHEET_TABLE, new String[] {
				TimesheetTable.KEY_ROWID, TimesheetTable.KEY_ENDDATE,
				TimesheetTable.KEY_SUBMITTED }, null, null, null, null, null,
				null);
	}

	public Cursor fetchTimesheet(long rowId) throws SQLException {
		Cursor mCursor = db.query(true, TimesheetTable.TIMESHEET_TABLE,
				new String[] { TimesheetTable.KEY_ROWID,
						TimesheetTable.KEY_ENDDATE,
						TimesheetTable.KEY_SUBMITTED },
				TimesheetTable.KEY_ROWID + "=" + rowId, null, null, null, null,
				null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	public Cursor fetchTimesheet(Date endDate) throws SQLException {
		Cursor mCursor = db.query(true, TimesheetTable.TIMESHEET_TABLE,
				new String[] { TimesheetTable.KEY_ROWID,
						TimesheetTable.KEY_ENDDATE,
						TimesheetTable.KEY_SUBMITTED },
				TimesheetTable.KEY_ENDDATE + " = '"
						+ new SimpleDateFormat(DB_DATE_FORMAT).format(endDate)
						+ "'", null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	public boolean timesheetSubmitted(Date endDate) {
		Cursor mCursor = db.query(true, TimesheetTable.TIMESHEET_TABLE,
				new String[] { TimesheetTable.KEY_SUBMITTED },
				TimesheetTable.KEY_ENDDATE + " = '"
						+ new SimpleDateFormat(DB_DATE_FORMAT).format(endDate)
						+ "'", null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}

		try {
			return mCursor.getInt(mCursor
					.getColumnIndexOrThrow(TimesheetTable.KEY_SUBMITTED)) > 0;
		} catch (CursorIndexOutOfBoundsException e) {
			return false;
		}

	}

	public Cursor fetchAllTimesheetEntries() {
		return db.query(TimesheetEntryTable.ENTRY_TABLE, new String[] {
				TimesheetEntryTable.KEY_ROWID,
				TimesheetEntryTable.KEY_TIMESHEET,
				TimesheetEntryTable.KEY_TYPE, TimesheetEntryTable.KEY_SUBTYPE,
				TimesheetEntryTable.KEY_HOURS, TimesheetEntryTable.KEY_NOTES },
				null, null, null, null, null, null);
	}

	public Cursor fetchTimesheetEntry(long rowId) throws SQLException {
		Cursor mCursor = db.query(true, TimesheetEntryTable.ENTRY_TABLE,
				new String[] { TimesheetEntryTable.KEY_ROWID,
						TimesheetEntryTable.KEY_TIMESHEET,
						TimesheetEntryTable.KEY_TYPE,
						TimesheetEntryTable.KEY_SUBTYPE,
						TimesheetEntryTable.KEY_HOURS,
						TimesheetEntryTable.KEY_NOTES },
				TimesheetEntryTable.KEY_ROWID + "=" + rowId, null, null, null,
				null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	private ContentValues createEntryValues(long timesheet, int day,
			String type, String subtype, int hours, String notes) {
		ContentValues values = new ContentValues();
		values.put(TimesheetEntryTable.KEY_TIMESHEET, timesheet);
		values.put(TimesheetEntryTable.KEY_DAY, day);
		values.put(TimesheetEntryTable.KEY_TYPE, type);
		values.put(TimesheetEntryTable.KEY_SUBTYPE, subtype);
		values.put(TimesheetEntryTable.KEY_HOURS, hours);
		values.put(TimesheetEntryTable.KEY_NOTES, notes);
		return values;
	}

	private ContentValues createTimesheetValues(Date endDate, boolean submitted) {
		ContentValues values = new ContentValues();
		values.put(TimesheetTable.KEY_ENDDATE, new SimpleDateFormat(
				DB_DATE_FORMAT).format(endDate));
		values.put(TimesheetTable.KEY_SUBMITTED, submitted);
		return values;
	}

	public boolean deleteTimesheetsForWeek(Date endDate) {
		return db.delete(TimesheetEntryTable.ENTRY_TABLE,
				TimesheetEntryTable.KEY_TIMESHEET + " = (SELECT "
						+ TimesheetTable.KEY_ROWID + " FROM "
						+ TimesheetTable.TIMESHEET_TABLE + " WHERE "
						+ TimesheetTable.KEY_ENDDATE + " = '"
						+ new SimpleDateFormat(DB_DATE_FORMAT).format(endDate)
						+ "')", null) > 0;
	}
}